﻿using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Models.RequestModel;
using RndMaterialDisposalService.Repository;
using RndMaterialDisposalService.Services.Interface.TemporaryDocument;
using Microsoft.Data.SqlClient;
using NPOI.POIFS.Crypt.Agile;
using System.Data;
using Newtonsoft.Json;
using Microsoft.EntityFrameworkCore;
using NPOI.SS.Formula.Functions;
using System.Collections.Generic;
using Masuit.Tools;
using Masuit.Tools.Models;
using Razor.Templating.Core;
using RndMaterialDisposalService.Helper;
using static NPOI.SS.Formula.Functions.LinearRegressionFunction;

namespace RndMaterialDisposalService.Services.TemporaryDocumentService
{
    [NAutowired(Lifecycle.Scoped)]
    public class TemporaryProcessDocumentService:ITemporaryProcessDocumentService
    {
        private readonly AssetsManagementContext dbContext;
        private readonly UserBaseInforDataContext userBaseInforDataContext;
        private readonly WorkflowCommonDataContext workflowCommonDataContext;
        private IConfiguration configuration;
        private readonly string encyptUserInfo = "/api/UserLoginInforDecrypt/Encypt";
        private readonly string MailSendUrl = "/api/MailSender/SendMailToQueue";

        public TemporaryProcessDocumentService(AssetsManagementContext _dbContext, UserBaseInforDataContext _userBaseInforDataContext, WorkflowCommonDataContext _workflowCommonDataContext, IConfiguration _configuration) 
        {
            dbContext = _dbContext;
            userBaseInforDataContext = _userBaseInforDataContext;
            workflowCommonDataContext = _workflowCommonDataContext;
            configuration = _configuration;
        }

        /// <summary>
        /// 返回临时工艺文件下发单报表数据（带分页）
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<TemporaryProcessDocumentReportFormModel> GetTemporaryProcessDocumentReportFormPageList(TemporaryProcessDocumentReportConditionModel input) 
        {
            List<TemporaryProcessDocumentReportFormModel> list = new List<TemporaryProcessDocumentReportFormModel>();
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(input.CurrentStatus))
            {
                condition += $"AND V.CurrentStatus = '{input.CurrentStatus}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.CreateName)) 
            {
                condition += $"AND V.CreateName = '{input.CreateName}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.AffiliationDepartment))
            {
                condition += $"AND V.AffiliationDepartment = '{input.AffiliationDepartment}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ProductType))
            {
                condition += $"AND V.ProductType = '{input.ProductType}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.Item))
            {
                condition += $"AND V.Item LIKE '%{input.Item}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ApplicationNumber))
            {
                condition += $"AND V.ApplicationNumber LIKE '%{input.ApplicationNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.Effectiveness))
            {
                condition += $"AND V.Effectiveness = '{input.Effectiveness}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ApplicantTime))
            {
                string[] timeArr = input.ApplicantTime.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND V.ApplicantTime >= '{timeArr[0]} 00:00:00' AND V.ApplicantTime <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(input.TemporaryFileName)) 
            {
                condition += $"AND V.TemporaryFileName LIKE '%{input.TemporaryFileName}%' ";
            }


            //if (!string.IsNullOrWhiteSpace(input.DepartmentName))
            //{
            //    condition += $"AND (V.LargeSectors LIKE '%{input.DepartmentName}%' OR  V.SmallSector LIKE '%{input.DepartmentName}%') ";
            //}
            //if (!string.IsNullOrWhiteSpace(input.AssetNo))
            //{
            //    condition += $"AND V.AssetNo LIKE '%'+@AssetNo+'%' ";
            //    SqlParameter sqlPara = new SqlParameter("@AssetNo", SqlDbType.NVarChar);
            //    sqlPara.Value = input.AssetNo;
            //    sqlParameters.Add(sqlPara);
            //}

            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                //获取当前用户临时工艺文件下发单电子流审批流程记录,去重后联查得到ProcInstId
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.TemporaryProcessDocumentBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber AND WorkflowName = '临时工艺文件下发单电子流')  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '临时工艺文件下发单电子流' AND ApproverName = '{input.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ProcInstId).ToList();

                //当前用户工号
                string employeeNumber = input.CurrentUser.Substring(0, input.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {
                    //获取发起流程实例
                    _sql = $"SELECT ProcInstId FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId !='' AND WorkflowName='临时工艺文件下发单电子流' AND ApplicantEmployeeNumber = '{employeeNumber}'";
                    List<WorkflowProcInstIdModel> LaunchList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());
                    if (LaunchList.Any())
                    {
                        var LaunchProcInstIdList = LaunchList.Select(a => a.ProcInstId).ToList();
                        ApprovalHistoryProcInstIdList.AddRange(LaunchProcInstIdList);
                        ApprovalHistoryProcInstIdList = ApprovalHistoryProcInstIdList.Distinct().ToList();
                    }
                }

                string ApplicationNumber_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ApplicationNumber_STR = string.Join(',', _ProcInstIdList);
                }

                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.TemporaryProcessDocumentBusinessData WHERE ProcInstId IN({ApplicationNumber_STR}))";
            }
            else
            {
                SqlInsertClip = "(SELECT * FROM AssetsManagement.dbo.TemporaryProcessDocumentBusinessData WHERE ProcInstId !='')";
            }

            int startIndex = ((input.pageIndex - 1) * input.pageSize) + 1;
            int endIndex = input.pageSize * input.pageIndex;

            string sql = @$"SELECT V.[Key],V.ApplicationNumber,V.ProcInstId,V.ApplicantEmployeeNumber,V.CreatedDate,V.CurrentStatus,V.ApplicantName
                                   ,V.ApplicantTime,V.CreateName,V.AffiliationDepartment,V.ProductType,V.TemporaryFileName,V.ChangeParts,V.Item
                                   ,V.Status,V.Effectiveness,V.TakeEffectTime,V.LoseEfficacyTime
                                   FROM (SELECT ROW_NUMBER()  OVER(ORDER BY V.CreatedDate DESC) AS RowID,V.* FROM (
                            SELECT temporary.SysId AS [Key],workflow.ApplicationNumber,workflow.ProcInstId,workflow.ApplicantEmployeeNumber,workflow.CreatedDate,workflow.CurrentStatus,workflow.CurrentHandler AS ApplicantName
                                   ,temporary.CreateTime AS ApplicantTime,temporary.CreateName,temporary.AffiliationDepartment,temporary.ProductType,temporary.TemporaryFileName,temporary.ChangeParts,temporary.Item
                                   ,temporary.Status,temporary.Effectiveness,temporary.TakeEffectTime,temporary.LoseEfficacyTime
                            FROM {SqlInsertClip} temporary
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON temporary.ProcInstId = workflow.ProcInstId) V WHERE {condition}) V 
                            WHERE V.RowID BETWEEN {startIndex} AND {endIndex}";

            list = SqlQuery<TemporaryProcessDocumentReportFormModel>(sql, sqlParameters);

            //赋值流程最早时间
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.ApplicantTime = item.ApproveDate;
                    }
                });
            }

            list.ForEach((x) =>
            {
                x.ApplicationNumber = x.ApplicationNumber.Trim();
                //有效性
                //x.Effectiveness = x.Effectiveness == true ? "有效" : "无效";
            });
            return list;
        }

        /// <summary>
        ///返回临时工艺文件下发单报表主数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<TemporaryProcessDocumentReportFormModel> GetTemporaryProcessDocumentReportFormData(TemporaryProcessDocumentReportConditionModel input) 
        {
            List<TemporaryProcessDocumentReportFormModel> list = new List<TemporaryProcessDocumentReportFormModel>();
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(input.CurrentStatus))
            {
                condition += $"AND workflow.CurrentStatus = '{input.CurrentStatus}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.CreateName))
            {
                condition += $"AND temporary.CreateName = '{input.CreateName}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.AffiliationDepartment))
            {
                condition += $"AND temporary.AffiliationDepartment = '{input.AffiliationDepartment}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ProductType))
            {
                condition += $"AND temporary.ProductType = '{input.ProductType}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.Item))
            {
                condition += $"AND temporary.Item LIKE '%{input.Item}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ApplicationNumber))
            {
                condition += $"AND workflow.ApplicationNumber LIKE '%{input.ApplicationNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.Effectiveness))
            {
                condition += $"AND temporary.Effectiveness = '{input.Effectiveness}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ApplicantTime))
            {
                string[] timeArr = input.ApplicantTime.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND temporary.CreateTime >= '{timeArr[0]} 00:00:00' AND temporary.CreateTime <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(input.TemporaryFileName))
            {
                condition += $"AND temporary.TemporaryFileName LIKE '%{input.TemporaryFileName}%' ";
            }

            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                //获取当前用户临时工艺文件下发单电子流审批流程记录,去重后联查得到ProcInstId
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.TemporaryProcessDocumentBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber  AND WorkflowName = '临时工艺文件下发单电子流')  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '临时工艺文件下发单电子流' AND ApproverName = '{input.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ProcInstId).ToList();

                //当前用户工号
                string employeeNumber = input.CurrentUser.Substring(0, input.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {
                    //获取发起流程实例
                    _sql = $"SELECT ApplicationNumber AS ProcInstId FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId !='' AND WorkflowName='临时工艺文件下发单电子流' AND ApplicantEmployeeNumber = '{employeeNumber}'";
                    List<WorkflowProcInstIdModel> LaunchList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());
                    if (LaunchList.Any())
                    {
                        var LaunchProcInstIdList = LaunchList.Select(a => a.ProcInstId).ToList();
                        ApprovalHistoryProcInstIdList.AddRange(LaunchProcInstIdList);
                        ApprovalHistoryProcInstIdList = ApprovalHistoryProcInstIdList.Distinct().ToList();
                    }
                }

                string ProcInstId_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ProcInstId_STR = string.Join(',', _ProcInstIdList);
                }

                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.TemporaryProcessDocumentBusinessData WHERE ProcessFolio IN({ProcInstId_STR}))";
            }
            else
            {
                SqlInsertClip = "(SELECT * FROM AssetsManagement.dbo.TemporaryProcessDocumentBusinessData WHERE ProcInstId !='')";
            }

            string sql = @$"SELECT temporary.SysId AS [Key],workflow.ApplicationNumber,workflow.ProcInstId,workflow.ApplicantEmployeeNumber,workflow.CreatedDate,workflow.CurrentStatus,workflow.CurrentHandler AS ApplicantName 
                                   ,temporary.CreateTime AS ApplicantTime,temporary.CreateName,temporary.AffiliationDepartment,temporary.ProductType,temporary.TemporaryFileName,temporary.ChangeParts,temporary.Item
                                   ,temporary.Status,temporary.Effectiveness,temporary.TakeEffectTime,temporary.LoseEfficacyTime
                            FROM {SqlInsertClip} temporary
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON temporary.ProcInstId = workflow.ProcInstId WHERE {condition}";

            list = SqlQuery<TemporaryProcessDocumentReportFormModel>(sql, sqlParameters);

            list.ForEach((x) => {
                x.ApplicationNumber = x.ApplicationNumber.Trim();
                //有效性
                //x.Effectiveness = x.Effectiveness == "1" ? "有效" : "无效";
            });

            //赋值流程最早时间
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.ApplicantTime = item.ApproveDate;
                    }
                });
            }
            return list;
        }

        /// <summary>
        /// 新增临时工艺文件下发单业务数据
        /// </summary>
        /// <param name="compensate"></param>
        /// <returns></returns>
        public async Task<int> AddTemporaryProcessDocumentBusinessData(TemporaryProcessDocumentBusinessDataModel temporaryProcessDocument) 
        {
            var item = await dbContext.TemporaryProcessDocumentBusinessData.FirstOrDefaultAsync(a => a.ProcessFolio == temporaryProcessDocument.ProcessFolio);
            if (item != null) 
            {
                dbContext.TemporaryProcessDocumentBusinessData.Remove(item);
            }

            dbContext.TemporaryProcessDocumentBusinessData.Add(temporaryProcessDocument);
            return await dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 批量判断工艺文件是否失效，失效则修改为失效状态
        /// </summary>
        /// <returns></returns>
        public async Task<bool> EditTemporaryEffectivenessTypeList() 
        {
            try
            {
                DateTime dt = DateTime.Now;
                var list = await dbContext.TemporaryProcessDocumentBusinessData.Where(a => a.Effectiveness == true && a.LoseEfficacyTime < dt).Take(50).ToListAsync();
                if (list.Any())
                {
                    List<ApplicationDataModel> applicationDataList = new List<ApplicationDataModel>();
                    List<TemporaryProcessDocumentBusinessDataModel> temporaryProcessDocumentBusinessDataList = new List<TemporaryProcessDocumentBusinessDataModel>();
                    List<EmailParameter> emailParameterList = new List<EmailParameter>();

                    list.ForEach((x) => {
                        x.Effectiveness = false;
                        //修改工艺文件流程表单失效性状态
                        var item = workflowCommonDataContext.ApplicationData.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                        if (item != null)
                        {
                            dynamic dyn = JsonConvert.DeserializeObject<dynamic>(item.FormData);
                            dyn.technology.Effectiveness = false;
                            item.FormData = JsonConvert.SerializeObject(dyn);
                            applicationDataList.Add(item);

                            EmailParameter emailParameterItem = new EmailParameter();
                            emailParameterItem.ProcInstId = x.ProcInstId.Trim();
                            emailParameterItem.Subject = $"临时工艺文件下发单电子流-{item.ApplicationNumber?.Trim()}-{item.SubmitBy}";
                            emailParameterItem.DocumentDistributionUserID = dyn.technology.DocumentDistributionUserID;
                            emailParameterList.Add(emailParameterItem);
                        }
                        temporaryProcessDocumentBusinessDataList.Add(x);
                    });

                    //更新流程实例表
                    workflowCommonDataContext.ApplicationData.AttachRange(applicationDataList);
                    await workflowCommonDataContext.SaveChangesAsync();
                    //更新数据报表
                    dbContext.TemporaryProcessDocumentBusinessData.AttachRange(temporaryProcessDocumentBusinessDataList);
                    await dbContext.SaveChangesAsync();

                    //失效发送邮件通知
                    InvalidEmailSending(emailParameterList);

                    ////修改报表失效性状态
                    //if (ProcInstIdList.Any())
                    //{
                    //    string sql = $"UPDATE AssetsManagement.dbo.TemporaryProcessDocumentBusinessData SET Effectiveness='0' WHERE ProcInstId IN({string.Join(',', ProcInstIdList)})";
                    //    var count = await dbContext.Database.ExecuteSqlRawAsync(sql);
                    //    state = count > 0 ? true : false;
                    //}
                }

                //失效发送邮件通知
                InvalidEmailSending(new List<EmailParameter>());
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 提前失效
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public async Task<int> EditTemporaryEffectivenessType(FallimentoPrecoceModel item) 
        {
            int count = 0;
            if (item != null) 
            {
                List<ApplicationDataModel> applicationDataList = new List<ApplicationDataModel>();
                List<TemporaryProcessDocumentBusinessDataModel> temporaryProcessDocumentBusinessDataList = new List<TemporaryProcessDocumentBusinessDataModel>();

                item.ProcInstIdList.ForEach((x) => {
                    var applicationDataItem = workflowCommonDataContext.ApplicationData.FirstOrDefault(a => a.ProcInstId == x);
                    if (applicationDataItem != null)
                    {
                        dynamic dyn = JsonConvert.DeserializeObject<dynamic>(applicationDataItem.FormData);
                        dyn.technology.Effectiveness = false;
                        applicationDataItem.FormData = JsonConvert.SerializeObject(dyn);
                        applicationDataList.Add(applicationDataItem);
                    }
                    var temporaryProcessDocumentBusinessDataItem = dbContext.TemporaryProcessDocumentBusinessData.FirstOrDefault(a => a.ProcInstId == x);
                    if (temporaryProcessDocumentBusinessDataItem != null)
                    {
                        temporaryProcessDocumentBusinessDataItem.Effectiveness = false;
                        temporaryProcessDocumentBusinessDataList.Add(temporaryProcessDocumentBusinessDataItem);
                    }
                });
                //更新流程实例表
                workflowCommonDataContext.ApplicationData.AttachRange(applicationDataList);
                count += await workflowCommonDataContext.SaveChangesAsync();
                //更新数据报表
                dbContext.TemporaryProcessDocumentBusinessData.AttachRange(temporaryProcessDocumentBusinessDataList);
                count += await dbContext.SaveChangesAsync();
            }

            return count;
        }

        /// <summary>
        /// 根据流程实例ID返回用户邮箱
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<List<DistributeFilesEmail>> GetUserEmailList(TemporaryProcessDocumentProcessProcInstIdModel Input) 
        {
            List<DistributeFilesEmail> distributeFilesEmail = new List<DistributeFilesEmail>();
            if (Input != null) 
            {
                List<string> ProcInstIdList = Input.ProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                string ProcInstIdStr = string.Join(',', ProcInstIdList);
                string sql = $"SELECT ProcInstId,FormData FROM VW_Common.dbo.WorkflowApplicationData WHERE WorkflowName='临时工艺文件下发单电子流' AND ProcInstId IN({ProcInstIdStr})";
                List<TemporaryProcessDocumentProcessFormDataModel> formDataList = SqlQuery<TemporaryProcessDocumentProcessFormDataModel>(sql, new List<SqlParameter>());
                if (formDataList.Any()) 
                {
                    formDataList.ForEach((x) => {
                        dynamic dyn = JsonConvert.DeserializeObject<dynamic>(x.FormData);
                        string userIdStr = dyn.technology.DocumentDistributionUserID;
                        if (!string.IsNullOrWhiteSpace(userIdStr)) 
                        {
                            string[] userIdArrar = userIdStr.Split(';');
                            userIdArrar.ForEach((y) => {
                                var item = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(a => a.UserId == y);
                                if (item != null) 
                                {
                                    distributeFilesEmail.Add(new DistributeFilesEmail {
                                        ProcInstId = x.ProcInstId,
                                        Email = item.Email
                                    });
                                }
                            });
                        }
                    });
                }
            }
            return distributeFilesEmail;
        }

        /// <summary>
        /// 失效发送邮件通知
        /// </summary>
        /// <param name="list"></param>
        public async void InvalidEmailSending(List<EmailParameter> list)
        {
            if (list.Any())
            {
                //提前获取下发文件用户
                List<string> distributeFilesUserList = list.Select(a => a.DocumentDistributionUserID).ToList();
                List<string> disDistributeFilesUserList = string.Join(";", distributeFilesUserList).Split(';').Distinct().ToList();
                var emailList = userBaseInforDataContext.UserBaseInfor.Where(a => disDistributeFilesUserList.Contains(a.UserId)).ToList();

                var html = await RazorTemplateEngine.RenderAsync("~/Pages/EmailTemplates/TemporaryProcessDocument/InvalidEmailSendingIndex.cshtml");
                if (!string.IsNullOrWhiteSpace(html)) 
                {
                    //获取加密字符串
                    string userId = "39220";
                    string functionId = "3911";
                    var CommonServiceHost = configuration.GetSection("CommonServiceHost").Value;

                    foreach (var x in list)
                    {
                        var url = $"{CommonServiceHost}{encyptUserInfo}?userID={userId}&functionID={functionId}&procInstID={x.ProcInstId}";
                        var response = await HttpHelper.GetAsync<string>(url);

                        string templateData = html;
                        templateData = templateData.Replace("\r\n", "");
                        templateData = templateData.Replace("\\", "");
                        templateData = templateData.Replace("${subject}", x.Subject);
                        templateData = templateData.Replace("${_Para}", response);

                        //配置邮箱参数
                        MailSendModel mailSend = new MailSendModel();
                        mailSend.mailID = 0;
                        mailSend.mailFrom = "eofficetest@vertiv.com";
                        //mailSend.mailTo = "邮箱";
                        mailSend.mailSubject = x.Subject;
                        mailSend.mailBody = templateData;
                        mailSend.isHtml = 1;
                        mailSend.mailStatus = 0;

                        //发送邮件路径
                        var url2 = $"{CommonServiceHost}{MailSendUrl}";

                        //获取下发人员邮箱
                        if (!string.IsNullOrWhiteSpace(x.DocumentDistributionUserID)) 
                        {
                            string[] userIdArrar = x.DocumentDistributionUserID.Split(';');

                            foreach (var y in userIdArrar)
                            {
                                var item = emailList.FirstOrDefault(a => a.UserId == y);
                                if (item == null) 
                                {
                                    continue;
                                }

                                mailSend.mailTo = item.Email;
                                mailSend.createdDate = DateTime.Now;
                                if (!string.IsNullOrWhiteSpace(mailSend.mailTo)) 
                                {
                                    await HttpHelper.PostAsync<int>(url2, JsonConvert.SerializeObject(mailSend));
                                }
                            };
                        }
                    };
                }
            }
        }

        /// <summary>
        /// 临时工艺文件下发回收闭环栏环节为审批通过，但已失效则发送邮件通知
        /// </summary>
        /// <returns></returns>
        public async Task<bool> ProcessIncompleteAndIneffective()
        {

            List<EmailParameter> emailParameterList = new List<EmailParameter>();
            //查询所有处于临时工艺文件下发回收闭环栏环节环节的电子流
            var list = await workflowCommonDataContext.ApplicationData.Where(a => a.WorkflowName == "临时工艺文件下发单电子流" && a.CurrentStatus== "待临时工艺文件下发回收闭环栏").ToListAsync();
            if (list.Any()) 
            {
                list.ForEach((x) => {
                    dynamic dyn = JsonConvert.DeserializeObject<dynamic>(x.FormData);
                    bool Effectiveness = Convert.ToBoolean(dyn.technology.Effectiveness);
                    if (!Effectiveness) 
                    {
                        EmailParameter emailParameterItem = new EmailParameter();
                        emailParameterItem.ProcInstId = x.ProcInstId.Trim();
                        emailParameterItem.Subject = $"临时工艺文件下发单电子流-{x.ApplicationNumber?.Trim()}-{x.SubmitBy}";
                        emailParameterItem.DocumentDistributionUserID = dyn.technology.DocumentDistributionUserID;
                        emailParameterList.Add(emailParameterItem);
                    }
                });
            }

            //失效发送邮件通知
            if (emailParameterList.Any()) 
            {
                InvalidEmailSending(emailParameterList);
            }
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sql, List<SqlParameter> sqlParameters) where T : class, new()
        {
            List<T> list = new List<T>();
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        //var fieid = t.GetType().GetField(columnName);
                        //fieid?.SetValue(t, item);
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }
    }
}
